1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmQuotationRecord1
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select RTRIM(QuotationNo), Date,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(Product.ProductCode),RTRIM(ProductName),Cost, Qty, DiscountPer, Quotation_Join.Discount, VATPer, Quotation_Join.VAT, TotalAmount, GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation,quotation_Join,Product where Customer.ID=quotation.CustomerID and Quotation.Q_ID=Quotation_Join.QuotationID and Product.PID=Quotation_Join.ProductID order by Date", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillQuotationNo()
25 End Sub
26
27
28 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
29 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
30 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
31 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
32 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
33 End If
34 Dim b As Brush = SystemBrushes.ControlText
35 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
36
37 End Sub
38 Sub fillQuotationNo()
39 Try
40 con = New SqlConnection(cs)
41 con.Open()
42 adp = New SqlDataAdapter()
43 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(QuotationNo) FROM quotation", con)
44 ds = New DataSet("ds")
45 adp.Fill(ds)
46 dtable = ds.Tables(0)
47 cmbQuotationNo.Items.Clear()
48 For Each drow As DataRow In dtable.Rows
49 cmbQuotationNo.Items.Add(drow(0).ToString())
50 Next
51 Catch ex As Exception
52 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
53 End Try
54 End Sub
55 Sub Reset()
56 cmbQuotationNo.Text = ""
57 txtCustomerName.Text = ""
58 fillQuotationNo()
59 dtpDateFrom.Text = Today
60 dtpDateTo.Text = Today
61 Getdata()
62 End Sub
63 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
64 Reset()
65 End Sub
66
67 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
68 Me.Close()
69 End Sub
70
71
72 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
73 Dim rowsTotal, colsTotal As Short
74 Dim I, j, iC As Short
75 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
76 Dim xlApp As New Excel.Application
77 Try
78 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
79 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
80 xlApp.Visible = True
81
82 rowsTotal = dgw.RowCount
83 colsTotal = dgw.Columns.Count - 1
84 With excelWorksheet
85 .Cells.Select()
86 .Cells.Delete()
87 For iC = 0 To colsTotal
88 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
89 Next
90 For I = 0 To rowsTotal - 1
91 For j = 0 To colsTotal
92 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
93 Next j
94 Next I
95 .Rows("1:1").Font.FontStyle = "Bold"
96 .Rows("1:1").Font.Size = 12
97
98 .Cells.Columns.AutoFit()
99 .Cells.Select()
100 .Cells.EntireColumn.AutoFit()
101 .Cells(1, 1).Select()
102 End With
103 Catch ex As Exception
104 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
105 Finally
106 'RELEASE ALLOACTED RESOURCES
107 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
108 xlApp = Nothing
109 End Try
110 End Sub
111
112 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
113 Try
114 con = New SqlConnection(cs)
115 con.Open()
116 cmd = New SqlCommand("Select RTRIM(QuotationNo), Date,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(Product.ProductCode),RTRIM(ProductName),Cost, Qty, DiscountPer, Quotation_Join.Discount, VATPer, Quotation_Join.VAT, TotalAmount, GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation,quotation_Join,Product where Customer.ID=quotation.CustomerID and Quotation.Q_ID=Quotation_Join.QuotationID and Product.PID=Quotation_Join.ProductID and Date between @d1 and @d2 order by Date", con)
117 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
118 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
119 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
120 dgw.Rows.Clear()
121 While (rdr.Read() = True)
122 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14))
123 End While
124 con.Close()
125 Catch ex As Exception
126 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
127 End Try
128 End Sub
129
130 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbQuotationNo.SelectedIndexChanged
131 Try
132 con = New SqlConnection(cs)
133 con.Open()
134 cmd = New SqlCommand("Select RTRIM(QuotationNo), Date,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(Product.ProductCode),RTRIM(ProductName),Cost, Qty, DiscountPer, Quotation_Join.Discount, VATPer, Quotation_Join.VAT, TotalAmount, GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation,quotation_Join,Product where Customer.ID=quotation.CustomerID and Quotation.Q_ID=Quotation_Join.QuotationID and Product.PID=Quotation_Join.ProductID and QuotationNo='" & cmbQuotationNo.Text & "' order by Date", con)
135 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
136 dgw.Rows.Clear()
137 While (rdr.Read() = True)
138 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14))
139 End While
140 con.Close()
141 Catch ex As Exception
142 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
143 End Try
144 End Sub
145
146 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
147 Try
148 con = New SqlConnection(cs)
149 con.Open()
150 cmd = New SqlCommand("Select RTRIM(QuotationNo), Date,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(Product.ProductCode),RTRIM(ProductName),Cost, Qty, DiscountPer, Quotation_Join.Discount, VATPer, Quotation_Join.VAT, TotalAmount, GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation,quotation_Join,Product where Customer.ID=quotation.CustomerID and Quotation.Q_ID=Quotation_Join.QuotationID and Product.PID=Quotation_Join.ProductID and Name like '%" & txtCustomerName.Text & "%' order by Date", con)
151 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
152 dgw.Rows.Clear()
153 While (rdr.Read() = True)
154 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14))
155 End While
156 con.Close()
157 Catch ex As Exception
158 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
159 End Try
160 End Sub
161
162 Private Sub cmbQuotationNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbQuotationNo.Format
163 If (e.DesiredType Is GetType(String)) Then
164 e.Value = e.Value.ToString.Trim
165 End If
166 End Sub
167 End Class
